using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Text;

using EntLibEC.Cmn;
using EntLibEC.Info.Online;

namespace EntLibEC.Dac.Online
{
    /// <summary>
    /// Summary description for PromotionDac.
    /// </summary>
    public class PromotionDac
    {

        public PromotionDac()
        {

        }

        public int InsertPromotionMaster(PromotionInfo oParam)
        {
            string sql = @"INSERT INTO Ent_Promotion_Master
                            (
                            SysNo, PromotionName, ExpiringFrom, ExpiringTo, 
                            CreateTime, CreateUserSysNo , Status,UseRangeDescription
                            )
                            VALUES (
                            @SysNo, @PromotionName, @ExpiringFrom, @ExpiringTo, 
                            @CreateTime, @CreateUserSysNo ,@Status,@UseRangeDescription
                            )";
            SqlCommand cmd = new SqlCommand(sql);

            SqlParameter paramSysNo = new SqlParameter("@SysNo", SqlDbType.Int, 4);
            SqlParameter paramPromotionName = new SqlParameter("@PromotionName", SqlDbType.NVarChar, 200);
            SqlParameter paramExpiringFrom = new SqlParameter("@ExpiringFrom", SqlDbType.DateTime);
            SqlParameter paramExpiringTo = new SqlParameter("@ExpiringTo", SqlDbType.DateTime);
            SqlParameter paramCreateTime = new SqlParameter("@CreateTime", SqlDbType.DateTime);
            SqlParameter paramCreateUserSysNo = new SqlParameter("@CreateUserSysNo", SqlDbType.Int, 4);
            SqlParameter paramStatus = new SqlParameter("@Status", SqlDbType.Int, 4);
            SqlParameter paramUseRangeDescription = new SqlParameter("@UseRangeDescription", SqlDbType.NVarChar, 500);

            if (oParam.SysNo != AppConst.IntNull)
                paramSysNo.Value = oParam.SysNo;
            else
                paramSysNo.Value = System.DBNull.Value;
            if (oParam.PromotionName != AppConst.StringNull)
                paramPromotionName.Value = oParam.PromotionName;
            else
                paramPromotionName.Value = System.DBNull.Value;
            if (oParam.ExpiringFrom != AppConst.DateTimeNull)
                paramExpiringFrom.Value = oParam.ExpiringFrom;
            else
                paramExpiringFrom.Value = System.DBNull.Value;
            if (oParam.ExpiringTo != AppConst.DateTimeNull)
                paramExpiringTo.Value = oParam.ExpiringTo;
            else
                paramExpiringTo.Value = System.DBNull.Value;
            if (oParam.CreateTime != AppConst.DateTimeNull)
                paramCreateTime.Value = oParam.CreateTime;
            else
                paramCreateTime.Value = System.DBNull.Value;
            if (oParam.CreateUserSysNo != AppConst.IntNull)
                paramCreateUserSysNo.Value = oParam.CreateUserSysNo;
            else
                paramCreateUserSysNo.Value = System.DBNull.Value;

            if (oParam.Status != AppConst.IntNull)
                paramStatus.Value = oParam.Status;
            else
                paramStatus.Value = System.DBNull.Value;

            if (oParam.UseRangeDescription != AppConst.StringNull)
                paramUseRangeDescription.Value = oParam.UseRangeDescription;
            else
                paramUseRangeDescription.Value = System.DBNull.Value;

            cmd.Parameters.Add(paramSysNo);
            cmd.Parameters.Add(paramPromotionName);
            cmd.Parameters.Add(paramExpiringFrom);
            cmd.Parameters.Add(paramExpiringTo);
            cmd.Parameters.Add(paramCreateTime);
            cmd.Parameters.Add(paramCreateUserSysNo);
            cmd.Parameters.Add(paramStatus);
            cmd.Parameters.Add(paramUseRangeDescription);

            return SqlHelper.ExecuteNonQuery(cmd);
        }

        public int UpdatePromotionMaster(PromotionInfo oParam)
        {

            string sql = @"UPDATE Ent_Promotion_Master SET 
                                    PromotionName=@PromotionName, 
                                    ExpiringFrom=@ExpiringFrom, ExpiringTo=@ExpiringTo, 
                                    CreateTime=@CreateTime, CreateUserSysNo=@CreateUserSysNo, 
                                    Status=@Status, UseRangeDescription=@UseRangeDescription
                                    WHERE SysNo=@SysNo";
            SqlCommand cmd = new SqlCommand(sql);

            SqlParameter paramSysNo = new SqlParameter("@SysNo", SqlDbType.Int, 4);
            SqlParameter paramPromotionName = new SqlParameter("@PromotionName", SqlDbType.NVarChar, 200);
            SqlParameter paramExpiringFrom = new SqlParameter("@ExpiringFrom", SqlDbType.DateTime);
            SqlParameter paramExpiringTo = new SqlParameter("@ExpiringTo", SqlDbType.DateTime);
            SqlParameter paramCreateTime = new SqlParameter("@CreateTime", SqlDbType.DateTime);
            SqlParameter paramCreateUserSysNo = new SqlParameter("@CreateUserSysNo", SqlDbType.Int, 4);
            SqlParameter paramStatus = new SqlParameter("@Status", SqlDbType.Int, 4);
            SqlParameter paramUseRangeDescription = new SqlParameter("@UseRangeDescription", SqlDbType.NVarChar, 500);

            if (oParam.SysNo != AppConst.IntNull)
                paramSysNo.Value = oParam.SysNo;
            else
                paramSysNo.Value = System.DBNull.Value;
            if (oParam.PromotionName != AppConst.StringNull)
                paramPromotionName.Value = oParam.PromotionName;
            else
                paramPromotionName.Value = System.DBNull.Value;
            if (oParam.ExpiringFrom != AppConst.DateTimeNull)
                paramExpiringFrom.Value = oParam.ExpiringFrom;
            else
                paramExpiringFrom.Value = System.DBNull.Value;
            if (oParam.ExpiringTo != AppConst.DateTimeNull)
                paramExpiringTo.Value = oParam.ExpiringTo;
            else
                paramExpiringTo.Value = System.DBNull.Value;
            if (oParam.CreateTime != AppConst.DateTimeNull)
                paramCreateTime.Value = oParam.CreateTime;
            else
                paramCreateTime.Value = System.DBNull.Value;
            if (oParam.CreateUserSysNo != AppConst.IntNull)
                paramCreateUserSysNo.Value = oParam.CreateUserSysNo;
            else
                paramCreateUserSysNo.Value = System.DBNull.Value;
            if (oParam.Status != AppConst.IntNull)
                paramStatus.Value = oParam.Status;
            else
                paramStatus.Value = System.DBNull.Value;
            if (oParam.UseRangeDescription != AppConst.StringNull)
                paramUseRangeDescription.Value = oParam.UseRangeDescription;
            else
                paramUseRangeDescription.Value = System.DBNull.Value;

            cmd.Parameters.Add(paramSysNo);
            cmd.Parameters.Add(paramPromotionName);
            cmd.Parameters.Add(paramExpiringFrom);
            cmd.Parameters.Add(paramExpiringTo);
            cmd.Parameters.Add(paramCreateTime);
            cmd.Parameters.Add(paramCreateUserSysNo);
            cmd.Parameters.Add(paramStatus);
            cmd.Parameters.Add(paramUseRangeDescription);

            return SqlHelper.ExecuteNonQuery(cmd);
        }

        public int UpdatePromotionMaster(Hashtable paramHash)
        {
            StringBuilder sb = new StringBuilder(200);
            sb.Append("UPDATE Ent_Promotion_Master SET ");

            if (paramHash != null && paramHash.Count != 0)
            {
                int index = 0;
                foreach (string key in paramHash.Keys)
                {
                    object item = paramHash[key];
                    if (key.ToLower() == "sysno")
                        continue;

                    if (index != 0)
                        sb.Append(",");
                    index++;


                    if (item is int || item is decimal)
                    {
                        sb.Append(key).Append("=").Append(item.ToString());
                    }
                    else if (item is string)
                    {
                        sb.Append(key).Append(" = ").Append(Util.ToSqlString(item.ToString()));
                    }
                    else if (item is DateTime)
                    {
                        sb.Append(key).Append(" = cast(").Append(Util.ToSqlString(((DateTime)item).ToString(AppConst.DateFormatLong))).Append(" as DateTime)");
                    }
                }
            }

            sb.Append(" WHERE SysNo=").Append(paramHash["SysNo"]);

            return SqlHelper.ExecuteNonQuery(sb.ToString());
        }


        public int InsertPromotionCode(PromotionCodeInfo oParam)
        {
            string sql = @"INSERT INTO Ent_Promotion_Code
                            (
                            SysNo, PromotionSysNo, PromotionCode, PromotionValue, 
                            UseCount, TotalCount, CreateTime, 
                            CreateUserSysNo , Status
                            )
                            VALUES (
                            @SysNo, @PromotionSysNo, @PromotionCode, @PromotionValue, 
                            @UseCount, @TotalCount, @CreateTime, 
                            @CreateUserSysNo , @Status
                            )";
            SqlCommand cmd = new SqlCommand(sql);

            SqlParameter paramSysNo = new SqlParameter("@SysNo", SqlDbType.Int, 4);
            SqlParameter paramPromotionSysNo = new SqlParameter("@PromotionSysNo", SqlDbType.Int, 4);
            SqlParameter paramPromotionCode = new SqlParameter("@PromotionCode", SqlDbType.NVarChar, 20);
            SqlParameter paramPromotionValue = new SqlParameter("@PromotionValue", SqlDbType.Decimal, 9);
            SqlParameter paramUseCount = new SqlParameter("@UseCount", SqlDbType.Int, 4);
            SqlParameter paramTotalCount = new SqlParameter("@TotalCount", SqlDbType.Int, 4);
            SqlParameter paramCreateTime = new SqlParameter("@CreateTime", SqlDbType.DateTime);
            SqlParameter paramCreateUserSysNo = new SqlParameter("@CreateUserSysNo", SqlDbType.Int, 4);
            SqlParameter paramStatus = new SqlParameter("@Status", SqlDbType.Int, 4);

            if (oParam.SysNo != AppConst.IntNull)
                paramSysNo.Value = oParam.SysNo;
            else
                paramSysNo.Value = System.DBNull.Value;
            if (oParam.PromotionSysNo != AppConst.IntNull)
                paramPromotionSysNo.Value = oParam.PromotionSysNo;
            else
                paramPromotionSysNo.Value = System.DBNull.Value;
            if (oParam.PromotionCode != AppConst.StringNull)
                paramPromotionCode.Value = oParam.PromotionCode;
            else
                paramPromotionCode.Value = System.DBNull.Value;
            if (oParam.PromotionValue != AppConst.DecimalNull)
                paramPromotionValue.Value = oParam.PromotionValue;
            else
                paramPromotionValue.Value = System.DBNull.Value;

            if (oParam.UseCount != AppConst.IntNull)
                paramUseCount.Value = oParam.UseCount;
            else
                paramUseCount.Value = System.DBNull.Value;
            if (oParam.TotalCount != AppConst.IntNull)
                paramTotalCount.Value = oParam.TotalCount;
            else
                paramTotalCount.Value = System.DBNull.Value;
            if (oParam.CreateTime != AppConst.DateTimeNull)
                paramCreateTime.Value = oParam.CreateTime;
            else
                paramCreateTime.Value = System.DBNull.Value;
            if (oParam.CreateUserSysNo != AppConst.IntNull)
                paramCreateUserSysNo.Value = oParam.CreateUserSysNo;
            else
                paramCreateUserSysNo.Value = System.DBNull.Value;

            if (oParam.Status != AppConst.IntNull)
                paramStatus.Value = oParam.Status;
            else
                paramStatus.Value = System.DBNull.Value;

            cmd.Parameters.Add(paramSysNo);
            cmd.Parameters.Add(paramPromotionSysNo);
            cmd.Parameters.Add(paramPromotionCode);
            cmd.Parameters.Add(paramPromotionValue);
            cmd.Parameters.Add(paramUseCount);
            cmd.Parameters.Add(paramTotalCount);
            cmd.Parameters.Add(paramCreateTime);
            cmd.Parameters.Add(paramCreateUserSysNo);
            cmd.Parameters.Add(paramStatus);

            return SqlHelper.ExecuteNonQuery(cmd);
        }
        public int UpdatePromotionCode(Hashtable paramHash)
        {
            StringBuilder sb = new StringBuilder(200);
            sb.Append("UPDATE Ent_Promotion_Code SET ");

            if (paramHash != null && paramHash.Count != 0)
            {
                int index = 0;
                foreach (string key in paramHash.Keys)
                {
                    object item = paramHash[key];
                    if (key.ToLower() == "sysno")
                        continue;

                    if (index != 0)
                        sb.Append(",");
                    index++;


                    if (item is int || item is decimal)
                    {
                        sb.Append(key).Append("=").Append(item.ToString());
                    }
                    else if (item is string)
                    {
                        sb.Append(key).Append(" = ").Append(Util.ToSqlString(item.ToString()));
                    }
                    else if (item is DateTime)
                    {
                        sb.Append(key).Append(" = cast(").Append(Util.ToSqlString(((DateTime)item).ToString(AppConst.DateFormatLong))).Append(" as DateTime)");
                    }
                }
            }

            sb.Append(" WHERE SysNo=").Append(paramHash["SysNo"]);

            return SqlHelper.ExecuteNonQuery(sb.ToString());
        }


        public int InsertPromotionProducts(PromotionProductInfo oParam)
        {
            string sql = @"INSERT INTO Ent_Promotion_Products
                            (
                            PromotionSysNo, ProductSysNo
                            )
                            VALUES (
                            @PromotionSysNo, @ProductSysNo
                            );set @SysNo = SCOPE_IDENTITY();";

            SqlCommand cmd = new SqlCommand(sql);

            SqlParameter paramSysNo = new SqlParameter("@SysNo", SqlDbType.Int, 4);
            SqlParameter paramPromotionSysNo = new SqlParameter("@PromotionSysNo", SqlDbType.Int, 4);
            SqlParameter paramProductSysNo = new SqlParameter("@ProductSysNo", SqlDbType.Int, 4);

            paramSysNo.Direction = ParameterDirection.Output;

            if (oParam.PromotionSysNo != AppConst.IntNull)
                paramPromotionSysNo.Value = oParam.PromotionSysNo;
            else
                paramPromotionSysNo.Value = System.DBNull.Value;
            if (oParam.ProductSysNo != AppConst.IntNull)
                paramProductSysNo.Value = oParam.ProductSysNo;
            else
                paramProductSysNo.Value = System.DBNull.Value;

            cmd.Parameters.Add(paramSysNo);
            cmd.Parameters.Add(paramPromotionSysNo);
            cmd.Parameters.Add(paramProductSysNo);

            return SqlHelper.ExecuteNonQuery(cmd);
        }

        public int UpdatePromotionProducts(Hashtable paramHash)
        {
            StringBuilder sb = new StringBuilder(200);
            sb.Append("UPDATE Ent_Promotion_Products SET ");

            if (paramHash != null && paramHash.Count != 0)
            {
                int index = 0;
                foreach (string key in paramHash.Keys)
                {
                    object item = paramHash[key];
                    if (key.ToLower() == "sysno")
                        continue;

                    if (index != 0)
                        sb.Append(",");
                    index++;


                    if (item is int || item is decimal)
                    {
                        sb.Append(key).Append("=").Append(item.ToString());
                    }
                    else if (item is string)
                    {
                        sb.Append(key).Append(" = ").Append(Util.ToSqlString(item.ToString()));
                    }
                    else if (item is DateTime)
                    {
                        sb.Append(key).Append(" = cast(").Append(Util.ToSqlString(((DateTime)item).ToString(AppConst.DateFormatLong))).Append(" as DateTime)");
                    }
                }
            }

            sb.Append(" WHERE SysNo=").Append(paramHash["SysNo"]);

            return SqlHelper.ExecuteNonQuery(sb.ToString());
        }

        public int DeletePromotionProducts(int sysno)
        {
            string sql = "delete from Ent_Promotion_Products where sysno=" + sysno;
            SqlCommand cmd = new SqlCommand(sql);
            return SqlHelper.ExecuteNonQuery(cmd);
        }

        public int DeletePromotionCode(int sysno)
        {
            string sql = "delete from Ent_Promotion_Code where sysno=" + sysno;
            SqlCommand cmd = new SqlCommand(sql);
            return SqlHelper.ExecuteNonQuery(cmd);
        }

        public int InsertPromotionCustomer(PromotionCustomerInfo oParam)
        {
            string sql = @"INSERT INTO Ent_Promotion_Customer
                            (
                            CustomerSysNo, PromotionCodeSysNo, SoSysNo
                            )
                            VALUES (
                            @CustomerSysNo, @PromotionCodeSysNo, @SoSysNo
                            );set @SysNo = SCOPE_IDENTITY();";

            SqlCommand cmd = new SqlCommand(sql);

            SqlParameter paramSysNo = new SqlParameter("@SysNo", SqlDbType.Int, 4);
            SqlParameter paramCustomerSysNo = new SqlParameter("@CustomerSysNo", SqlDbType.Int, 4);
            SqlParameter paramPromotionCodeSysNo = new SqlParameter("@PromotionCodeSysNo", SqlDbType.Int, 4);
            SqlParameter paramSoSysNo = new SqlParameter("@SoSysNo", SqlDbType.Int, 4);

            paramSysNo.Direction = ParameterDirection.Output;

            if (oParam.CustomerSysNo != AppConst.IntNull)
                paramCustomerSysNo.Value = oParam.CustomerSysNo;
            else
                paramCustomerSysNo.Value = System.DBNull.Value;
            if (oParam.PromotionCodeSysNo != AppConst.IntNull)
                paramPromotionCodeSysNo.Value = oParam.PromotionCodeSysNo;
            else
                paramPromotionCodeSysNo.Value = System.DBNull.Value;
            if (oParam.SoSysNo != AppConst.IntNull)
                paramSoSysNo.Value = oParam.SoSysNo;
            else
                paramSoSysNo.Value = System.DBNull.Value;

            cmd.Parameters.Add(paramSysNo);
            cmd.Parameters.Add(paramCustomerSysNo);
            cmd.Parameters.Add(paramPromotionCodeSysNo);
            cmd.Parameters.Add(paramSoSysNo);

            return SqlHelper.ExecuteNonQuery(cmd);
        }
        public int UpdatePromotionCustomer(Hashtable paramHash)
        {
            StringBuilder sb = new StringBuilder(200);
            sb.Append("UPDATE Ent_Promotion_Customer SET ");

            if (paramHash != null && paramHash.Count != 0)
            {
                int index = 0;
                foreach (string key in paramHash.Keys)
                {
                    object item = paramHash[key];
                    if (key.ToLower() == "sysno")
                        continue;

                    if (index != 0)
                        sb.Append(",");
                    index++;


                    if (item is int || item is decimal)
                    {
                        sb.Append(key).Append("=").Append(item.ToString());
                    }
                    else if (item is string)
                    {
                        sb.Append(key).Append(" = ").Append(Util.ToSqlString(item.ToString()));
                    }
                    else if (item is DateTime)
                    {
                        sb.Append(key).Append(" = cast(").Append(Util.ToSqlString(((DateTime)item).ToString(AppConst.DateFormatLong))).Append(" as DateTime)");
                    }
                    else if (item is System.DBNull)
                    {
                        sb.Append(key).Append("= null ");
                    }
                }
            }

            sb.Append(" WHERE SysNo=").Append(paramHash["SysNo"]);

            return SqlHelper.ExecuteNonQuery(sb.ToString());
        }

        //		public int UpdatePromotionCustomerSoSysNo(int sosysno)
        //		{
        //		    string sql = "update Ent_Promotion_Customer set  sosysno is null where sosysno=" + sosysno ;
        //
        //			SqlCommand cmd = new SqlCommand(sql);
        //
        //			return SqlHelper.ExecuteNonQuery(cmd);
        //
        //		}

        public int DeletePromotionCustomer(int sysno)
        {
            string sql = "delete from Ent_Promotion_Customer where sysno=" + sysno;
            SqlCommand cmd = new SqlCommand(sql);
            return SqlHelper.ExecuteNonQuery(cmd);
        }


        public int InsertPromotionLimit(PromotionLimitInfo oParam)
        {
            string sql = @"INSERT INTO Ent_Promotion_Limit
                            (
                            PromotionSysNo, LimitType, C1SysNo, 
                            C2SysNo, C3SysNo, CategoryName, ManufacturerSysNo, 
                            ManufacturerName, ItemPriceLowerLimit, ProductSysNo, ProductID, 
                            SOAmtLowerLimit, Status
                            )
                            VALUES (
                            @PromotionSysNo, @LimitType, @C1SysNo, 
                            @C2SysNo, @C3SysNo, @CategoryName, @ManufacturerSysNo, 
                            @ManufacturerName, @ItemPriceLowerLimit, @ProductSysNo, @ProductID, 
                            @SOAmtLowerLimit, @Status
                            );select @@IDENTITY";

            SqlCommand cmd = new SqlCommand(sql);

            SqlParameter paramPromotionSysNo = new SqlParameter("@PromotionSysNo", SqlDbType.Int, 4);
            SqlParameter paramLimitType = new SqlParameter("@LimitType", SqlDbType.Int, 4);
            SqlParameter paramC1SysNo = new SqlParameter("@C1SysNo", SqlDbType.Int, 4);
            SqlParameter paramC2SysNo = new SqlParameter("@C2SysNo", SqlDbType.Int, 4);
            SqlParameter paramC3SysNo = new SqlParameter("@C3SysNo", SqlDbType.Int, 4);
            SqlParameter paramCategoryName = new SqlParameter("@CategoryName", SqlDbType.NVarChar, 200);
            SqlParameter paramManufacturerSysNo = new SqlParameter("@ManufacturerSysNo", SqlDbType.Int, 4);
            SqlParameter paramManufacturerName = new SqlParameter("@ManufacturerName", SqlDbType.NVarChar, 100);
            SqlParameter paramItemPriceLowerLimit = new SqlParameter("@ItemPriceLowerLimit", SqlDbType.Decimal, 9);
            SqlParameter paramProductSysNo = new SqlParameter("@ProductSysNo", SqlDbType.Int, 4);
            SqlParameter paramProductID = new SqlParameter("@ProductID", SqlDbType.NVarChar, 20);
            SqlParameter paramSOAmtLowerLimit = new SqlParameter("@SOAmtLowerLimit", SqlDbType.Int, 4);
            SqlParameter paramStatus = new SqlParameter("@Status", SqlDbType.Int, 4);


            if (oParam.PromotionSysNo != AppConst.IntNull)
                paramPromotionSysNo.Value = oParam.PromotionSysNo;
            else
                paramPromotionSysNo.Value = System.DBNull.Value;
            if (oParam.LimitType != AppConst.IntNull)
                paramLimitType.Value = oParam.LimitType;
            else
                paramLimitType.Value = System.DBNull.Value;
            if (oParam.C1SysNo != AppConst.IntNull)
                paramC1SysNo.Value = oParam.C1SysNo;
            else
                paramC1SysNo.Value = System.DBNull.Value;
            if (oParam.C2SysNo != AppConst.IntNull)
                paramC2SysNo.Value = oParam.C2SysNo;
            else
                paramC2SysNo.Value = System.DBNull.Value;
            if (oParam.C3SysNo != AppConst.IntNull)
                paramC3SysNo.Value = oParam.C3SysNo;
            else
                paramC3SysNo.Value = System.DBNull.Value;
            if (oParam.CategoryName != AppConst.StringNull)
                paramCategoryName.Value = oParam.CategoryName;
            else
                paramCategoryName.Value = System.DBNull.Value;
            if (oParam.ManufacturerSysNo != AppConst.IntNull)
                paramManufacturerSysNo.Value = oParam.ManufacturerSysNo;
            else
                paramManufacturerSysNo.Value = System.DBNull.Value;
            if (oParam.ManufacturerName != AppConst.StringNull)
                paramManufacturerName.Value = oParam.ManufacturerName;
            else
                paramManufacturerName.Value = System.DBNull.Value;
            if (oParam.ItemPriceLowerLimit != AppConst.DecimalNull)
                paramItemPriceLowerLimit.Value = oParam.ItemPriceLowerLimit;
            else
                paramItemPriceLowerLimit.Value = System.DBNull.Value;
            if (oParam.ProductSysNo != AppConst.IntNull)
                paramProductSysNo.Value = oParam.ProductSysNo;
            else
                paramProductSysNo.Value = System.DBNull.Value;
            if (oParam.ProductID != AppConst.StringNull)
                paramProductID.Value = oParam.ProductID;
            else
                paramProductID.Value = System.DBNull.Value;
            if (oParam.SOAmtLowerLimit != AppConst.IntNull)
                paramSOAmtLowerLimit.Value = oParam.SOAmtLowerLimit;
            else
                paramSOAmtLowerLimit.Value = System.DBNull.Value;
            if (oParam.Status != AppConst.IntNull)
                paramStatus.Value = oParam.Status;
            else
                paramStatus.Value = System.DBNull.Value;

            cmd.Parameters.Add(paramPromotionSysNo);
            cmd.Parameters.Add(paramLimitType);
            cmd.Parameters.Add(paramC1SysNo);
            cmd.Parameters.Add(paramC2SysNo);
            cmd.Parameters.Add(paramC3SysNo);
            cmd.Parameters.Add(paramCategoryName);
            cmd.Parameters.Add(paramManufacturerSysNo);
            cmd.Parameters.Add(paramManufacturerName);
            cmd.Parameters.Add(paramItemPriceLowerLimit);
            cmd.Parameters.Add(paramProductSysNo);
            cmd.Parameters.Add(paramProductID);
            cmd.Parameters.Add(paramSOAmtLowerLimit);
            cmd.Parameters.Add(paramStatus);

            return Convert.ToInt32(SqlHelper.ExecuteScalar(cmd));
        }


        public int UpdatePromotionLimit(PromotionLimitInfo oParam)
        {
            string sql = @"UPDATE Ent_Promotion_Limit SET 
                            PromotionSysNo=@PromotionSysNo, 
                            LimitType=@LimitType, C1SysNo=@C1SysNo, 
                            C2SysNo=@C2SysNo, C3SysNo=@C3SysNo, 
                            CategoryName=@CategoryName, ManufacturerSysNo=@ManufacturerSysNo, 
                            ManufacturerName=@ManufacturerName, ItemPriceLowerLimit=@ItemPriceLowerLimit, 
                            ProductSysNo=@ProductSysNo, ProductID=@ProductID, 
                            SOAmtLowerLimit=@SOAmtLowerLimit, Status=@Status
                            WHERE SysNo=@SysNo";
            SqlCommand cmd = new SqlCommand(sql);

            SqlParameter paramSysNo = new SqlParameter("@SysNo", SqlDbType.Int, 4);
            SqlParameter paramPromotionSysNo = new SqlParameter("@PromotionSysNo", SqlDbType.Int, 4);
            SqlParameter paramLimitType = new SqlParameter("@LimitType", SqlDbType.Int, 4);
            SqlParameter paramC1SysNo = new SqlParameter("@C1SysNo", SqlDbType.Int, 4);
            SqlParameter paramC2SysNo = new SqlParameter("@C2SysNo", SqlDbType.Int, 4);
            SqlParameter paramC3SysNo = new SqlParameter("@C3SysNo", SqlDbType.Int, 4);
            SqlParameter paramCategoryName = new SqlParameter("@CategoryName", SqlDbType.NVarChar, 200);
            SqlParameter paramManufacturerSysNo = new SqlParameter("@ManufacturerSysNo", SqlDbType.Int, 4);
            SqlParameter paramManufacturerName = new SqlParameter("@ManufacturerName", SqlDbType.NVarChar, 100);
            SqlParameter paramItemPriceLowerLimit = new SqlParameter("@ItemPriceLowerLimit", SqlDbType.Decimal, 9);
            SqlParameter paramProductSysNo = new SqlParameter("@ProductSysNo", SqlDbType.Int, 4);
            SqlParameter paramProductID = new SqlParameter("@ProductID", SqlDbType.NVarChar, 20);
            SqlParameter paramSOAmtLowerLimit = new SqlParameter("@SOAmtLowerLimit", SqlDbType.Int, 4);
            SqlParameter paramStatus = new SqlParameter("@Status", SqlDbType.Int, 4);

            if (oParam.SysNo != AppConst.IntNull)
                paramSysNo.Value = oParam.SysNo;
            else
                paramSysNo.Value = System.DBNull.Value;
            if (oParam.PromotionSysNo != AppConst.IntNull)
                paramPromotionSysNo.Value = oParam.PromotionSysNo;
            else
                paramPromotionSysNo.Value = System.DBNull.Value;
            if (oParam.LimitType != AppConst.IntNull)
                paramLimitType.Value = oParam.LimitType;
            else
                paramLimitType.Value = System.DBNull.Value;
            if (oParam.C1SysNo != AppConst.IntNull)
                paramC1SysNo.Value = oParam.C1SysNo;
            else
                paramC1SysNo.Value = System.DBNull.Value;
            if (oParam.C2SysNo != AppConst.IntNull)
                paramC2SysNo.Value = oParam.C2SysNo;
            else
                paramC2SysNo.Value = System.DBNull.Value;
            if (oParam.C3SysNo != AppConst.IntNull)
                paramC3SysNo.Value = oParam.C3SysNo;
            else
                paramC3SysNo.Value = System.DBNull.Value;
            if (oParam.CategoryName != AppConst.StringNull)
                paramCategoryName.Value = oParam.CategoryName;
            else
                paramCategoryName.Value = System.DBNull.Value;
            if (oParam.ManufacturerSysNo != AppConst.IntNull)
                paramManufacturerSysNo.Value = oParam.ManufacturerSysNo;
            else
                paramManufacturerSysNo.Value = System.DBNull.Value;
            if (oParam.ManufacturerName != AppConst.StringNull)
                paramManufacturerName.Value = oParam.ManufacturerName;
            else
                paramManufacturerName.Value = System.DBNull.Value;
            if (oParam.ItemPriceLowerLimit != AppConst.DecimalNull)
                paramItemPriceLowerLimit.Value = oParam.ItemPriceLowerLimit;
            else
                paramItemPriceLowerLimit.Value = System.DBNull.Value;
            if (oParam.ProductSysNo != AppConst.IntNull)
                paramProductSysNo.Value = oParam.ProductSysNo;
            else
                paramProductSysNo.Value = System.DBNull.Value;
            if (oParam.ProductID != AppConst.StringNull)
                paramProductID.Value = oParam.ProductID;
            else
                paramProductID.Value = System.DBNull.Value;
            if (oParam.SOAmtLowerLimit != AppConst.IntNull)
                paramSOAmtLowerLimit.Value = oParam.SOAmtLowerLimit;
            else
                paramSOAmtLowerLimit.Value = System.DBNull.Value;
            if (oParam.Status != AppConst.IntNull)
                paramStatus.Value = oParam.Status;
            else
                paramStatus.Value = System.DBNull.Value;

            cmd.Parameters.Add(paramSysNo);
            cmd.Parameters.Add(paramPromotionSysNo);
            cmd.Parameters.Add(paramLimitType);
            cmd.Parameters.Add(paramC1SysNo);
            cmd.Parameters.Add(paramC2SysNo);
            cmd.Parameters.Add(paramC3SysNo);
            cmd.Parameters.Add(paramCategoryName);
            cmd.Parameters.Add(paramManufacturerSysNo);
            cmd.Parameters.Add(paramManufacturerName);
            cmd.Parameters.Add(paramItemPriceLowerLimit);
            cmd.Parameters.Add(paramProductSysNo);
            cmd.Parameters.Add(paramProductID);
            cmd.Parameters.Add(paramSOAmtLowerLimit);
            cmd.Parameters.Add(paramStatus);

            return SqlHelper.ExecuteNonQuery(cmd);
        }

        public int DeletePromotionLimit(int limitSysNo)
        {
            string sql = "delete from Ent_Promotion_Limit where sysno={0}";
            return SqlHelper.ExecuteNonQuery(String.Format(sql, limitSysNo));
        }
    }
}
